from flask import Flask, render_template, request

from sqlhelper import select

app = Flask(__name__)


@app.route('/')
def hello():
    return render_template('index.html')


@app.route('/index')
def index():
    return render_template('index.html')


@app.route('/zhaopin')
def zhaopin():
    data_list = []
    data = select('SELECT * FROM zhaopin')
    for i in data:
        data_list.append(i)
    return render_template('zhaopin.html', data=data_list)


@app.route('/zhaopin', methods=['GET', 'POST'])
def zhaopin2():
    data_list = []
    name = request.form.get("name")
    sql = 'SELECT * FROM zhaopin WHERE jobName like\'%' + str(name) + '%\''
    data = select(sql)
    for i in data:
        data_list.append(i)
    return render_template('zhaopin.html', data=data_list)


@app.route('/xueli')
def xueli():
    data_list = []
    data = select('SELECT degree,COUNT(*) FROM zhaopin GROUP BY degree')
    for i in data:
        data_list.append({'value': i[1], 'name': i[0]})
    return render_template('xueli.html', data_list=data_list)

@app.route('/gangwei')
def gangwei():
    name = []
    value = []
    data = select('SELECT industry,COUNT(*) FROM zhaopin GROUP BY industry ORDER BY COUNT(*) asc')
    for i in data:
        name.append(i[0])
        value.append(i[1])
    return render_template('gangwei.html', name=name, value=value)


@app.route('/gongsi')
def gongsi():
    name = []
    value = []
    data = select('SELECT comName,COUNT(*) FROM zhaopin GROUP BY comName ORDER BY COUNT(*) DESC LIMIT 10')
    for i in data:
        name.append(i[0])
        value.append(i[1])
    return render_template('gongsi.html', name=name, value=value)


@app.route('/gongzi')
def gongzi():
    data_list = []
    gz_list = []
    where = ['面议', '工资<3000', '3000<工资<5000', '5000<工资<8000', '8000<工资<10000', '10000<工资<20000', '工资>20000']
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 < 1')
    for i in data:
        data_list.append(i[0])
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 BETWEEN 1 AND 3000')
    for i in data:
        data_list.append(i[0])
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 BETWEEN 3000 AND 5000')
    for i in data:
        data_list.append(i[0])
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 BETWEEN 5000 AND 8000')
    for i in data:
        data_list.append(i[0])
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 BETWEEN 8000 AND 10000')
    for i in data:
        data_list.append(i[0])
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 BETWEEN 10000 AND 20000')
    for i in data:
        data_list.append(i[0])
    data = select('SELECT COUNT(*) FROM zhaopin WHERE (min_salary+max_salary)/2 >20000')
    for i in data:
        data_list.append(i[0])
    for i in range(len(where)):
        j = {'value': data_list[i], 'name': where[i]}
        gz_list.append(j)
    return render_template('gongzi.html', gz_list=gz_list)


@app.route('/chengshi')
def chengshi():
    name = []
    value = []
    data = select('SELECT city,COUNT(*) FROM zhaopin GROUP BY city ORDER BY COUNT(*) DESC LIMIT 10')
    for i in data:
        name.append(i[0])
        value.append(i[1])
    return render_template('chengshi.html', name=name, value=value)


@app.route('/jingyan')
def jingyan():
    data_list = []
    data = select('SELECT workExp,COUNT(*) FROM zhaopin GROUP BY workExp ')
    for i in data:
        data_list.append({'value': i[1], 'name': i[0]})
    return render_template('jingyan.html', data_list=data_list)


@app.route('/csgz')
def csgz():
    data_list = [['product', '最低', '最高']]
    data = select('SELECT city,COUNT(*), min(min_salary+0),MAX(max_salary+0) FROM zhaopin WHERE NOT min_salary=\'面议\' GROUP BY city ORDER BY COUNT(*) DESC LIMIT 10')
    for i in data:
        data_list.append([i[0], i[2], i[3]])
    return render_template('csgz.html', data_list=data_list)


@app.route('/ciyun')
def ciyun():

    return render_template('ciyun.html')



if __name__ == '__main__':
    app.run(debug=True)
